--POUR CONTROLE
/*remise dans le bon sens de masque-var*/
--select * from masque_var_trans order by id, COL05
DROP TABLE masque_ficvar_trans;
CREATE TABLE masque_ficvar_trans AS (
SELECT  ID,lig,
        max(Decode(RANGMULTI2,0,lig01,null)) col01,
        max(Decode(RANGMULTI2,1,lig01,null)) col02,
        max(Decode(RANGMULTI2,2,lig01,null)) col03,
        max(Decode(RANGMULTI2,3,lig01,null)) col04,
        max(Decode(RANGMULTI2,4,lig01,null)) col05,
        max(Decode(RANGMULTI2,5,lig01,null)) col06,
        max(Decode(RANGMULTI2,6,lig01,null)) col07,
        max(Decode(RANGMULTI2,7,lig01,null)) col08,
        max(Decode(RANGMULTI2,8,lig01,null)) col09,
        max(Decode(RANGMULTI2,9,lig01,null)) col10,
        max(Decode(RANGMULTI2,10,lig01,null)) col11,
        max(Decode(RANGMULTI2,11,lig01,null)) col12,
        max(Decode(RANGMULTI2,12,lig01,null)) col13,
        max(Decode(RANGMULTI2,13,lig01,null)) col14,
        max(Decode(RANGMULTI2,14,lig01,null)) col15,
        max(Decode(RANGMULTI2,15,lig01,null)) col16,
        max(Decode(RANGMULTI2,16,lig01,null)) col17,
        max(Decode(RANGMULTI2,17,lig01,null)) col18,
        max(Decode(RANGMULTI2,18,lig01,null)) col19,
        max(Decode(RANGMULTI2,19,lig01,null)) col20,
        max(Decode(RANGMULTI2,20,lig01,null)) col21,
        max(Decode(RANGMULTI2,21,lig01,null)) col22,
        max(Decode(RANGMULTI2,22,lig01,null)) col23,
        max(Decode(RANGMULTI2,23,lig01,null)) col24,
        max(Decode(RANGMULTI2,24,lig01,null)) col25,
        max(Decode(RANGMULTI2,25,lig01,null)) col26,
        max(Decode(RANGMULTI2,26,lig01,null)) col27,
        max(Decode(RANGMULTI2,27,lig01,null)) col28,
        max(Decode(RANGMULTI2,28,lig01,null)) col29,
        max(Decode(RANGMULTI2,29,lig01,null)) col30,
        max(Decode(RANGMULTI2,30,lig01,null)) col31,
        max(Decode(RANGMULTI2,31,lig01,null)) col32,
        max(Decode(RANGMULTI2,32,lig01,null)) col33,
        max(Decode(RANGMULTI2,33,lig01,null)) col34,
        max(Decode(RANGMULTI2,34,lig01,null)) col35,
        max(Decode(RANGMULTI2,35,lig01,null)) col36,
        max(Decode(RANGMULTI2,36,lig01,null)) col37,
        max(Decode(RANGMULTI2,37,lig01,null)) col38,
        max(Decode(RANGMULTI2,38,lig01,null)) col39,
        max(Decode(RANGMULTI2,39,lig01,null)) col40,
        max(Decode(RANGMULTI2,40,lig01,null)) col41,
        max(Decode(RANGMULTI2,41,lig01,null)) col42,
        max(Decode(RANGMULTI2,42,lig01,null)) col43,
        max(Decode(RANGMULTI2,43,lig01,null)) col44,
        max(Decode(RANGMULTI2,44,lig01,null)) col45,
        max(Decode(RANGMULTI2,45,lig01,null)) col46,
        max(Decode(RANGMULTI2,46,lig01,null)) col47,
        max(Decode(RANGMULTI2,47,lig01,null)) col48,
        max(Decode(RANGMULTI2,48,lig01,null)) col49,
        max(Decode(RANGMULTI2,49,lig01,null)) col50,
        max(Decode(RANGMULTI2,50,lig01,null)) col51,
        max(Decode(RANGMULTI2,51,lig01,null)) col52,
        max(Decode(RANGMULTI2,52,lig01,null)) col53,
        max(Decode(RANGMULTI2,53,lig01,null)) col54,
        max(Decode(RANGMULTI2,54,lig01,null)) col55
FROM (
 SELECT 'lig01' lig ,l.* FROM uv_pmasque_ficvar_lig01 l
 UNION ALL
 SELECT 'lig02' lig ,l.* FROM uv_pmasque_ficvar_lig02 l
 UNION ALL
 SELECT 'lig03' lig ,l.* FROM uv_pmasque_ficvar_lig03 l
 UNION ALL
 SELECT 'lig04' lig ,l.* FROM uv_pmasque_ficvar_lig04 l
 UNION ALL
 SELECT 'lig05' lig ,l.* FROM uv_pmasque_ficvar_lig05 l
 UNION ALL
 SELECT 'lig06' lig ,l.* FROM uv_pmasque_ficvar_lig06 l
 UNION ALL
 SELECT 'lig07' lig ,l.* FROM uv_pmasque_ficvar_lig07 l
 UNION ALL
 SELECT 'lig08' lig ,l.* FROM uv_pmasque_ficvar_lig08 l
 UNION ALL
 SELECT 'lig09' lig ,l.* FROM uv_pmasque_ficvar_lig09 l
 UNION ALL
 SELECT 'lig10' lig ,l.* FROM uv_pmasque_ficvar_lig10 l
 UNION ALL
 SELECT 'lig11' lig ,l.* FROM uv_pmasque_ficvar_lig11 l
 UNION ALL
 SELECT 'lig12' lig ,l.* FROM uv_pmasque_ficvar_lig12 l
 UNION ALL
 SELECT 'lig13' lig ,l.* FROM uv_pmasque_ficvar_lig13 l
 UNION ALL
 SELECT 'lig14' lig ,l.* FROM uv_pmasque_ficvar_lig14 l
 UNION ALL
 SELECT 'lig15' lig ,l.* FROM uv_pmasque_ficvar_lig10 l
 UNION ALL
 SELECT 'lig16' lig ,l.* FROM uv_pmasque_ficvar_lig11 l
 UNION ALL
 SELECT 'lig17' lig ,l.* FROM uv_pmasque_ficvar_lig12 l
 UNION ALL
 SELECT 'lig18' lig ,l.* FROM uv_pmasque_ficvar_lig13 l
 UNION ALL
 SELECT 'lig19' lig ,l.* FROM uv_pmasque_ficvar_lig14 l

) ll
GROUP BY id,lig  );


define erp='erp_mli2.';
define outils='outils_mli2.';

 --CALL uvse_ExistsOrCreate('VRT_GRILLE');

 DELETE FROM se_VRT_GRILLE;






 INSERT INTO se_VRT_GRILLE
 (SELECT  DISTINCT REPLACE(m.id,'.MOLI','') grille_cod,
          0 enreg_id,
          (SELECT Max(SubStr(Trim(REPLACE(REPLACE(Nvl(Nvl(mm.col08,mm.col28),mm.col21),'$A',''),':')),0,30)) FROM masque_var_trans mm WHERE mm.col22=m.id) grille_lib,
          NULL grille_lib2,
          'N' enreg_s,
          'T' grille_typ
     FROM  masque_ficvar_trans m
     WHERE EXISTS(SELECT 1 FROM masque_var_trans mm WHERE mm.col22=m.id)

     );
  DELETE  se_VRT_GRILLE WHERE  GRILLE_COD IN ('FOURNISSEURS','COLLECTIONS','LIGNES')   ;

 UPDATE se_VRT_GRILLE tf SET enreg_id=Nvl((select enreg_id FROM &erp.VRT_GRILLE f WHERE f.grille_cod=tf.grille_cod),0) ;
 UPDATE se_VRT_GRILLE tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;


 CALL uvse_synchroverserp('grille_cod','VRT_GRILLE','O') ;


 commit;

 
select * from (
SELECT &outils.stragg(source) source,grille_cod, enreg_id, grille_lib, grille_lib2, enreg_s, grille_typ
FROM (
  SELECT 'uv' source ,g.* FROM se_VRT_GRILLE g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.VRT_GRILLE g
  )

GROUP BY grille_cod, enreg_id, grille_lib, grille_lib2, enreg_s, grille_typ
HAVING Count(1)!=2 ) r
ORDER BY grille_cod  ;
